﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using FOModels.DepositSaving;
namespace DataAccess.DepositSaving
{

    public class DepositSavingDAL
    {
        private SQLDataAccess db;
        public DepositSavingDAL()
        {
            db = new SQLDataAccess();
        }
        public DataTable getTermSavingDefaultByProvider(int CompanyID, int langid)
        {
            return db.exeSelect("Select * from SavingDeposit sa join SavingDepositLang sl on sa.SavingDepositID = sl.SavingDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang comla on co.CompanyID = comla.CompanyID join TableTermInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.SavingDepositCategoryID = 1 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and  ta.[Month] = 1 and sa.Display = 1 and comla.LangID = " + langid + " and sl.LangID = " + langid + " and sa.CompanyID = " + CompanyID + "");
        }
        public DataTable getdepositsavingrelati(int CompanyID, int DepositSavingID, int langid)
        {
            return db.exeSelect("select * from SavingDeposit ca join SavingDepositLang sl on ca.SavingDepositID = sl.SavingDepositID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where ca.CompanyID =" + CompanyID + " and ca.SavingDepositID !=" + DepositSavingID + " and cl.LangID = " + langid + " and sl.LangID=" + langid + "");
        }
        public DataTable getDepositSavingList(int cateid, string balance, int month, int langid)
        {
            return db.exeSelect("Select * from SavingDeposit sa join SavingDepositLang sl on sa.SavingDepositID = sl.SavingDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang comla on co.CompanyID = comla.CompanyID join TableTermInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.SavingDepositCategoryID = " + cateid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and  ta.[Month] = " + month + " and sa.Display = 1 and comla.LangID = " + langid + " and sl.LangID = " + langid + "");
        }
        public DataTable getDepositSavingListByProvider(int cateid, string balance, int month, int CompanyID, int langid)
        {
            return db.exeSelect("Select * from SavingDeposit sa join SavingDepositLang sl on sa.SavingDepositID = sl.SavingDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang comla on co.CompanyID = comla.CompanyID join TableTermInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.SavingDepositCategoryID = " + cateid + " and sa.MinimumBalance <= " + balance + " and sa.MaximumBalance >= " + balance + " and  ta.[Month] = " + month + " and sa.Display = 1 and comla.LangID = " + langid + " and sl.LangID = " + langid + " and sa.CompanyID = " + CompanyID + "");
        }
        public DataTable getTermSavingDefault(int langid)
        {
            return db.exeSelect("Select * from SavingDeposit sa join SavingDepositLang sl on sa.SavingDepositID = sl.SavingDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang comla on co.CompanyID = comla.CompanyID join TableTermInterest ta on co.CompanyID = ta.CompanyID where co.Active = 1 and sa.SavingDepositCategoryID = 1 and sa.MinimumBalance <= 50000000 and sa.MaximumBalance >= 50000000 and  ta.[Month] = 1 and sa.Display = 1 and comla.LangID = " + langid + " and sl.LangID = " + langid + "");
        }
        public DataTable getall(int langid)
        {
            return db.exeSelect("Select * from SavingDeposit sa join SavingDepositLang sl on sa.SavingDepositID = sl.SavingDepositID join Company co on sa.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID join Language la on sl.LangID = la.ID where cl.LangID = " + langid + " and sl.LangID = " + langid + "");
        }
        public DataTable getDepositSavingCategory(int langid)
        {
            return db.exeSelect("Select * from SavingDepositCategory s join SavingDepositCategoryLang sl on s.SavingDepositCategoryID = sl.SavingDepositCategoryID where LangID=" + langid + "");
        }

        public DataTable getDepositSavingByID(int SavingDepositID, int langid)
        {
            return db.exeSelect("select * from SavingDeposit ca join SavingDepositLang sl on ca.SavingDepositID = sl.SavingDepositID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = " + langid + " and sl.LangID = " + langid + " and ca.SavingDepositID = " + SavingDepositID + "");
        }
        public DataTable getDepositSavingByIDManager(int SavingDepositID, int langid)
        {
            return db.exeSelect("select * from SavingDeposit ca join SavingDepositLang sl on ca.SavingDepositID = sl.SavingDepositID join Company co on ca.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where cl.LangID = " + langid + " and sl.LangID = " + langid + " and ca.SavingDepositID = " + SavingDepositID + "");
        }
        public DataTable gettop1termsaving()
        {
            return db.exeSelect("select top 1 * from SavingDeposit order by SavingDepositID desc");
        }
        public int UpdateDepositSaving(DepositSavingModels models)
        {
            return db.exeUpdate("update SavingDeposit set SavingDepositName = N'" + models.DepositSavingName + "',MinimumBalance=" + double.Parse(models.MinimumBalance) + ",MaximumBalance=" + double.Parse(models.MaximumBalance) + ",SavingDepositCategoryID=" + models.SavingDepositCategoryID + ",CompanyID= " + models.CompanyID + ",Display='" + models.Display + "' where SavingDepositID = " + models.DepositSavingID + "");
        }
        public int UpdateDepositSavingLang(DepositSavingModels models, int langid)
        {
            return db.exeUpdate("update SavingDepositLang set SavingDepositName = N'" + models.DepositSavingName + "',Description=N'" + models.Description + "',Benefit=N'" + models.Benefit + "',FeeContent=N'" + models.FeeContent + "',MoreInfo=N'" + models.MoreInfo + "' where SavingDepositID = " + models.DepositSavingID + " and LangID = " + langid + "");
        }
        public int InsertDepositSavingLang(DepositSavingModels models, int langid)
        {
            return db.exeUpdate("insert into SavingDepositLang values (" + models.DepositSavingID + "," + langid + ",N'" + models.DepositSavingName + "',N'" + models.Description + "',N'" + models.Benefit + "',N'" + models.FeeContent + "',N'" + models.MoreInfo + "')");
        }
        public int InsertDepositSaving(DepositSavingModels models)
        {
            return db.exeUpdate("insert into SavingDeposit values (N'" + models.DepositSavingName + "'," + double.Parse(models.MinimumBalance) + "," + models.CompanyID + "," + double.Parse(models.MaximumBalance) + "," + models.SavingDepositCategoryID + ",'" + models.Display + "')");
        }
        public int DeleteDepositSaving(int SavingDepositID)
        {
            return db.exeUpdate("Delete from SavingDeposit where SavingDepositID = " + SavingDepositID + "");
        }
        public int DeleteDepositSavingLang(int SavingDepositID)
        {
            return db.exeUpdate("Delete from SavingDepositLang where SavingDepositID = " + SavingDepositID + "");
        }

        public int DeleteDepositSavingApply(int SavingDepositApplyID)
        {
            return db.exeUpdate("Delete from SavingDepositApply where SavingDepositApplyID = " + SavingDepositApplyID + "");
        }
        public int UpdateStatus(DepositSavingApplyModels card)
        {
            return db.exeUpdate("update SavingDepositApply set StatusID= " + card.StatusID + " where SavingDepositApplyID = " + card.DepositSavingApplyID + "");
        }
        public int DepositSavingApply(int SavingDepositID, DepositSavingApplyModels app)
        {
            return db.exeUpdate("insert into SavingDepositApply(SavingDepositID,CustomerName,CustomerEmail,CustomerPhone,CustomerIncome,CustomerDOB,StatusID) values (" + SavingDepositID + ",N'" + app.CustomerName + "',N'" + app.CustomerEmail + "',N'" + app.CustomerPhone + "',N'" + app.CustomerIncome + "',N'" + app.CustomerDOB + "'," + app.StatusID + ")");
        }
        public DataTable getcontractbyid(int cardapplyid)
        {
            return db.exeSelect("select * from SavingDepositApply ca join SavingDeposit c on ca.SavingDepositID = c.SavingDepositID join [Status] ta on ca.StatusID = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and ca.SavingDepositApplyID = " + cardapplyid + " and cl.LangID = 1");
        }
        public DataTable getcontract()
        {
            return db.exeSelect("select * from SavingDepositApply ca join [SavingDeposit] c on ca.SavingDepositID = c.SavingDepositID join [Status] ta on ca.StatusID  = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1");
        }
        public DataTable getcontractbystatus(int statusid)
        {
            return db.exeSelect("select * from SavingDepositApply ca join [SavingDeposit] c on ca.SavingDepositID = c.SavingDepositID join [Status] ta on ca.StatusID  = ta.StatusID join Company co on c.CompanyID = co.CompanyID join CompanyLang cl on co.CompanyID = cl.CompanyID where co.Active = 1 and cl.LangID = 1 and ca.StatusID = " + statusid + "");
        }
    }
}
